package com.example.studentsystem.db;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import androidx.annotation.Nullable;

import com.example.studentsystem.Bean.Student;
import com.example.studentsystem.Bean.Course;

import java.util.ArrayList;
import java.util.List;

public class DBHelper extends SQLiteOpenHelper {
    private static final String TAG = "DBHelper";
    private static final String TABLE_NAME_STUDENT = "student";
    private static final String TABLE_NAME_COURSE = "course";

    private static final String TABLE_NAME_STUDENT_COURSE = "student_course"; // 关联表
    private static final String TABLE_NAME_SETTINGS = "settings"; // 设置表
    private String COLUMN_Student_PassWord="password";
    private String COLUMN_ID="student_id";
    private static DBHelper mHelper = null;

    Context mcontext;
    public DBHelper(@Nullable Context context) {
        super(context, "AllInformation", null, 1);
        this.mcontext=context;
    }
    public static DBHelper getInstance(Context context){
        if(mHelper == null){
            mHelper = new DBHelper(context);
        }
        return mHelper;
    }


    @Override
    public void onCreate(SQLiteDatabase db) {
        initTable(db);
        initData(db);
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 2) { // 数据库 1 -> 2 升级
            // 创建设置表
            db.execSQL("CREATE TABLE IF NOT EXISTS " +
                    TABLE_NAME_SETTINGS +
                    " (keycolumn text primary key,valuecolumn text)"
            );
        }
    }
    private void initTable(SQLiteDatabase db){
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_STUDENT + " (" +
                "student_id integer primary key autoincrement," +
                "student_name text," +
                "student_code text," +
                "username text," +
                "password text," +
                "class_name text)");
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_COURSE + " (" +
                "course_id integer primary key autoincrement," +
                "course_name text," +
                "course_teacher text," +
                "studytime text)");
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_STUDENT_COURSE + " (" +
                "student_id integer," +
                "course_id  integer," +
                "FOREIGN KEY (student_id) REFERENCES student(_id)," +
                "FOREIGN KEY (course_id) REFERENCES course(_id))");
    }

    private void initData(SQLiteDatabase db) {
        db.execSQL("insert into " + TABLE_NAME_STUDENT +
                " (student_name , student_code, username, password, class_name) " +
                "values" +
                "('张老三', '202100000001', 'helloworld', '12345678', '计算机1班')");
        db.execSQL("insert into " + TABLE_NAME_STUDENT +
                " (student_name , student_code, username, password, class_name) " +
                "values" +
                "('李老四', '202100000002', 'helloworld', 'abcdefgh', '计算机2班')");

        db.execSQL("insert into " + TABLE_NAME_COURSE +
                " (course_name , course_id, course_teacher, studytime) " +
                "values" +
                "('Android', '99996666', '张老师', '80')");

    }

    public int insertStudent2(Student student) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("student_name", student.getStudent_name());
        values.put("student_code", student.getStudent_code());
        values.put("username", student.getUsername());
        values.put("password", student.getPassword());
        values.put("class_name", student.getClass_name());
        long newRowId = db.insert(TABLE_NAME_STUDENT, null, values);
        Log.d(TAG, "insertStudent2 newRowId = " + newRowId);
        return (int) newRowId;
    }
    public int insertCourse2(Course course) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("course_name", course.getCourse_name());
        values.put("course_id", course.getCourse_id());
        values.put("course_teacher", course.getCourse_teacher());
        values.put("studytime", course.getStudytime());
        long newCouId = db.insert(TABLE_NAME_COURSE, null, values);
        Log.d(TAG, "insertCourse2 newCouId = " + newCouId);
        return (int) newCouId;
    }
    public int UpdatePassWord(Student student) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_Student_PassWord, student.getPassword());
        String whereClause = COLUMN_ID + " = ?";
        String[] whereArgs = { String.valueOf(student.getStudent_id()) };
        int count = db.update(TABLE_NAME_STUDENT, values, whereClause, whereArgs);
        db.close();
        return count;
    }
    public Cursor selectAllStudent() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = null;
        try {
            cursor = db.query(
                    TABLE_NAME_STUDENT, // 表名
                    null,               // 选择所有列
                    null,               // WHERE 子句
                    null,               // WHERE 子句的参数
                    null,               // GROUP BY
                    null,               // HAVING
                    "student_id ASC"    // ORDER BY
            );
            if (cursor == null) {
                return null;
            }
            return cursor;
        } catch (Exception e) {
            if (cursor != null) {
                cursor.close();
            }
            Log.e(TAG, "Error selecting all students", e);
            return null;
        }
    }
    @SuppressLint("Range")
    public Student login(String username, String password) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(
                TABLE_NAME_STUDENT,                                                        // 表名
                null,                                                                      // 要查询的列数据
                "(student_code = ?) and password = ?",                                      // 条件语句
                new String[] {username, password},                                         // 条件语句的值
                null,                                                                      // group by
                null,                                                                      // having
                null                                                                       // 排序语句
        );
        Student student = null;
        if (cursor != null && cursor.getCount() > 0) {
            if (cursor.moveToFirst()) {
                student = new Student();
                student.setStudent_id(cursor.getInt(cursor.getColumnIndex("student_id")));
                student.setStudent_name(cursor.getString(cursor.getColumnIndex("student_name")));
                student.setStudent_code(cursor.getString(cursor.getColumnIndex("student_code")));
                student.setUsername(cursor.getString(cursor.getColumnIndex("username")));
                student.setPassword(cursor.getString(cursor.getColumnIndex("password")));
                student.setClass_name(cursor.getString(cursor.getColumnIndex("class_name")));
                Log.d(TAG, "login = " + student.toString());
            }
        }

        return student;
    }


}